<?php

namespace app\service;


use app\model\EntityModel;
use app\model\EpisodeStepCategorySealTimeModel;
use app\model\NoteEntityRelationModel;
use app\model\PlanModel;
use app\model\ProjectMemberModel;
use app\model\ReviewRequestModel;
use app\model\StatusModel;
use app\model\TaskModel;
use app\model\StepCategoryModel;
use app\model\TaskRelationTagModel;
use app\model\TaskWorkOrderModel;
use app\tool\SingletonTrait;
use support\Db;
use support\Request;
use think\db\exception\DataNotFoundException;
use think\db\exception\DbException;
use think\db\exception\ModelNotFoundException;

class ProductionManageService
{
    use SingletonTrait;

    /**
     * 今日统计
     * @param $param
     * @return array|int[]
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function todayCount($param): array
    {
        $todayCountReturnData = [
            'done' => 0, // 已完成的
            'total' => 0, // 任务总数
            'abnormal' => 0,
            'in_progress' => 0,
            'repair_num' => 0,// 返修
            'abnormal_num' => 0,// 异常的
            'not_start_num' => 0,// 未开始的
            'not_final_num' => 0,// 未完成的
            'delay_final_num' => 0,// 延期完成的
        ];
        if (!empty($param['start_time'])) {
            $startTime = $param['start_time'];
        } else {
            $startTime = strtotime(date('Y-m-d 00:00:00'), time());
        }
        if (!empty($param['end_time'])) {
            $endTime = $param['end_time'];
        } else {
            $endTime = $startTime + 60 * 60 * 24;
        }
        $userInfo = UserService::getInstance()->getMyUserInfo(); // todo 建议改为前端传过来当前用户信息
        if (empty($userInfo)) {
            return $todayCountReturnData;
        }
        // 处理用户所在部门ids
        $projectMemberUserIdList = [];
        if (!empty($param['special_role_filter']) && $param['special_role_filter'] == 'director') {
            $projectMemberUserIdList = UserService::getInstance()->getRoleDepartmentIds($param['project_id'], 'director');
            if (empty($projectMemberUserIdList)) {
                return $todayCountReturnData;
            }
        }
        $taskIds = [];
        if (!empty($param['date_type']) && $param['date_type'] == 'delay_created') {
            $taskIds = $this->getDelayTaskIds($param['start_time'], $param['end_time']);
            if (empty($taskIds)) {
                return $todayCountReturnData;
            }
        }
        $totalQ = $this->getTodayBaseQuery($param, $userInfo, $startTime, $endTime, $projectMemberUserIdList, $taskIds);
        $totalData = $totalQ->field('count(distinct task.id) as Total')->find();
        if (!empty($totalData['Total'])) {
            $todayCountReturnData['total'] = intval($totalData['Total']);
            return $todayCountReturnData;
        }
        $doneQ = $this->getTodayBaseQuery($param, $userInfo, $startTime, $endTime, $projectMemberUserIdList, $taskIds);
        $doneData = $doneQ->field('count(distinct task.id) as PlanDone')
            ->whereRaw("get_json_string(task.`json`, 'task_status') IN (7,8)")
            ->find();
        if (!empty($doneData['PlanDone'])) {
            $todayCountReturnData['done'] = intval($totalData['PlanDone']);
            return $todayCountReturnData;
        }
        $inprogressQ = $this->getTodayBaseQuery($param, $userInfo, $startTime, $endTime, $projectMemberUserIdList, $taskIds);
        $inprogressData = $inprogressQ
            ->whereRaw("get_json_string(task.`json`, '$.task_status') in (select id from status where correspond = 'in_progress')")
            ->field('count(distinct task.id) as in_progress')
            ->find();
        if (!empty($inprogressData['in_progress'])) {
            $todayCountReturnData['in_progress'] = $inprogressData['in_progress'];
            return $todayCountReturnData;
        }
        $nowTime = time();
        $abnormalQ = $this->getTodayBaseQuery($param, $userInfo, $startTime, $endTime, $projectMemberUserIdList, $taskIds);
        $AbnormalData = $abnormalQ->field('count(distinct task.id) as Abnormal')
            ->join("(select task_id from task_work_order where status = 0 group by task_id) task_work_order_temp", 'task_work_order_temp.task_id = task.id', 'LEFT')
            ->whereRaw("(task.step_type = 'step' and (task.assign_status = 'default' or get_json_string(task.`json`, 'task_status') in (select id from status where code in ('not_started','ready_to_start','wait_assemble'))) and task.plan_end_time between 1 and {$nowTime} and get_json_string(task.`json`, 'task_status') not in (3, 7)) or (task.plan_end_time between 1 and {$nowTime} and get_json_string(task.`json`, 'task_status') not in (3, 7)) or task_work_order_temp.task_id > 0 ")
            ->find();
        if (!empty($AbnormalData['Abnormal'])) {
            $todayCountReturnData['abnormal'] = intval($AbnormalData['Abnormal']);
            return $todayCountReturnData;
        }
        if (!empty($param['is_my_responsible'])) {
            $todayCountReturnData = $this->getMyResponsible($param, $userInfo, $startTime, $endTime, $projectMemberUserIdList, $taskIds);
        }
        return $todayCountReturnData;
    }

    /**
     * 今日统计
     * @param int $projectId
     * @param string $specialRoleFilter
     * @param array $todoStepCategoryId
     * @param int $startTime
     * @param int $endTime
     * @param array $taskStepType
     * @param int $executor
     * @param bool $isMyResponsible
     * @param int $departmentId
     * @param array $executorUserIdList
     * @param string $dateType
     * @param bool $onlyNotDone
     * @param string $standardOrOther
     * @return int[]
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function todayCountForMyResponsibleTask(
        int    $projectId,
        string $specialRoleFilter,
        array  $todoStepCategoryId,
        int    $startTime,
        int    $endTime,
        array  $taskStepType,
        int    $executor,
        bool   $isMyResponsible,
        int    $departmentId,
        array  $executorUserIdList,
        string $dateType,
        bool   $onlyNotDone,
        string $standardOrOther
    ): array
    {
        $res = [
            'total' => 0,
            'done' => 0,
            'in_progress' => 0,
            'abnormal' => 0,
            'delay_final_num' => 0,
            'not_final_num' => 0,
            'not_start_num' => 0,
            'abnormal_num' => 0,
            'repair_num' => 0,
        ];

        if (empty($startTime)) {
            $timeRange = get_day_range_unix();
            $startTime = $timeRange['sdate'];
            $endTime = $timeRange['edate'];
        }
        if (empty($endTime)) {
            $endTime = strtotime('+1 day', $startTime);
        }

        $userInfo = UserService::getInstance()->getMyUserInfo(); // todo 建议改为前端传过来当前用户信息
        if (empty($userInfo)) {
            return $res;
        }

        $projectMemberUserIdList = [];
        switch ($specialRoleFilter) {
            case "director":
                $role = [];
                $directorRoleRes = RoleService::getInstance()->find('director');
                $projectMemberList = ProjectMemberService::getInstance()->select($projectId, $directorRoleRes);
                $projectMemberUserIdList = array_column($projectMemberList['data']['rows'], 'user_id');
                break;
        }

        $taskIds = [];
        if ($dateType == 'delay_created') {
            $taskModule = ModuleService::getInstance()->byCode('task');
            $taskIds = ReviewRequestModel::where('module_id', $taskModule['id'])
                ->whereBetween('created', [$startTime, $endTime])
                ->column('link_id');
            if (empty($taskIds)) {
                return $res;
            }
        }

        $currentTime = time();
        $statusSubSql = StatusModel::where('correspond', 'in_progress')->field('id')->buildSql(true);
        $statusSql = StatusService::getInstance()->byCodeMap(["not_started", "ready_to_start", "wait_assemble"], false, 'id');
        $abnormalConditionSql = ' task_work_order_temp.task_id is not null '
            . ' or ( '
//            . ' task.step_type = "step" '
//            . ' and ( task.assign_status = "default" '
//            . ' or get_json_string(task.`json`, "$.task_status") in (' . $statusSql . ')) '
//            . ' ) and '
            . ' task.plan_end_time between 1 and ' . $currentTime
            . ' and get_json_string(task.`json`, "$.task_status") not in (3,7) '
            . ' ) ';
        $params = [
            'project_id' => $projectId,
            'special_role_filter' => $specialRoleFilter,
            'todo_step_category_id' => $todoStepCategoryId,
            'task_step_type' => $taskStepType,
            'executor' => $executor,
            'department_id' => $departmentId,
            'executor_user_id_list' => $executorUserIdList,
            'date_type' => $dateType,
            'only_not_done' => $onlyNotDone
        ];

        $twoJoin = TaskWorkOrderModel::where('status', 0)->group('task_id')->field('task_id')->buildSql();
        $countDbr = self::getTodayBaseQuery($params, $userInfo, $startTime, $endTime, $projectMemberUserIdList, $taskIds)
            ->join([$twoJoin => 'task_work_order_temp'], 'task_work_order_temp.task_id = task.id', 'left')
            ->fieldRaw(' count(distinct task.id) as total, '
                . ' count(distinct if(get_json_string(task.`json`, "$.task_status") in (7,8), task.id, null)) as plan_done, '
                . ' count(distinct if(get_json_string(task.`json`, "$.task_status") in (' . $statusSubSql . '), task.id, null)) as in_progress, '
                . ' count(distinct if(' . $abnormalConditionSql . ', task.id, null)) as abnormal ');

        if ($isMyResponsible) {
            $statusSubSql = StatusService::getInstance()->byCodeMap(['ready_to_publish', 'final', 'closed'], false, 'id');
            $notStartedStatsList = StatusService::getInstance()->getNotStartedStatus();
            $notStartedStatusIdList = array_column($notStartedStatsList, 'id');
            $countDbr = $countDbr
                ->join(['repair_order_affected_task' => 'roat'], 'roat.task_id = task.id', 'left')
                ->fieldRaw('count(distinct if(get_json_string(task.`json`, "$.task_status") not in (' . $statusSubSql . '), task.id, null)) as not_final_num, '
                    . 'count(distinct if(get_json_string(task.`json`, "$.task_status") in (' . implode(',', $notStartedStatusIdList) . '), task.id, null )) as not_start_num, '
                    . 'count(distinct roat.task_id) as repair_num ');
        }
        $taskFilter = ['task.step_type' => ['NEQ', 'standard_make_to_do']];
        $count = $countDbr->where($taskFilter)->select()->toArray();
        $count = array_shift($count);

        $res['total'] = intval($count['total']);
        $res['done'] = intval($count['plan_done']);
        $res['in_progress'] = intval($count['in_progress']);
        $res['abnormal'] = intval($count['abnormal']);
        // 查询未完成的任务数
        $res['not_final_num'] = intval($count['not_final_num'] ?? 0);
        // 查询未开始
        $res['not_start_num'] = intval($count['not_start_num'] ?? 0);
        // 查询返修
        $res['repair_num'] = intval($count['repair_num'] ?? 0);

        return $res;
    }

    /**
     * 获取延期任务ids
     * @param $startTime
     * @param $endTime
     * @return array
     */
    public function getDelayTaskIds($startTime, $endTime): array
    {
        $taskList = (new \app\model\ReviewRequestModel)->field('link_id')
            ->where('module_id', ModuleService::getInstance()->byCode('task')['id'])
            ->whereBetween("created", [$startTime, $endTime]);
        if (empty($taskList->toArray())) {
            return [];
        }
        $taskIds = [];
        foreach ($taskList->toArray() as $taskData) {
            $taskIds[] = $taskData['link_id'];
        }
        return $taskIds;
    }

    /**
     * @param $param
     * @param $userInfo
     * @param $startTime
     * @param $endTime
     * @param $projectMemberUserIdList
     * @param $taskIds
     * @return array|int[]
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getMyResponsible($param, $userInfo, $startTime, $endTime, $projectMemberUserIdList, $taskIds): array
    {
        $todayCountReturnData = [
            'done' => 0, // 已完成的
            'total' => 0, // 任务总数
            'abnormal' => 0,
            'in_progress' => 0,
            'repair_num' => 0,// 返修
            'abnormal_num' => 0,// 异常的
            'not_start_num' => 0,// 未开始的
            'not_final_num' => 0,// 未完成的
            'delay_final_num' => 0,// 延期完成的
        ];
        // 查询未完成的任务数
        $notFinalNumQ = $this->getTodayBaseQuery($param, $userInfo, $startTime, $endTime, $projectMemberUserIdList, $taskIds);
        $notFinalNumData = $notFinalNumQ
            ->where("get_json_string(task.`json`, 'task_status') IN (select id from status where code not in ('ready_to_publish','final','closed'))")
            ->field("count(distinct task.id) as NotFinalNum")
            ->find();
        if (!empty($notFinalNumData['NotFinalNum'])) {
            $todayCountReturnData['not_final_num'] = intval($notFinalNumData['NotFinalNum']);
            return $todayCountReturnData;
        }
        // 查询未开始
        $notStartedStatusList = StatusService::getInstance()->getNotStartedStatus();
        if (empty($notStartedStatusList)) {
            return $todayCountReturnData;
        }
        $notStartedStatusIds = [];
        foreach ($notStartedStatusList as $notStartedStatusItem) {
            $notStartedStatusIds[] = $notStartedStatusItem['id'];
        }
        $notStartedStatusIds = join(',', $notStartedStatusIds);
        $notStartNumQ = $this->getTodayBaseQuery($param, $userInfo, $startTime, $endTime, $projectMemberUserIdList, $taskIds);
        $notStartNumData = $notStartNumQ
            ->whereRaw("get_json_string(task.`json`, 'task_status') IN ({$notStartedStatusIds})")
            ->field('count(distinct task.id) as NotStartNum')
            ->find();
        if (!empty($notStartNumData['NotStartNum'])) {
            $todayCountReturnData['NotStartNum'] = intval($notStartNumData['NotStartNum']);
            return $todayCountReturnData;
        }
        // 查询返修
        $repairNumData = $this->getTodayBaseQuery($param, $userInfo, $startTime, $endTime, $projectMemberUserIdList, $taskIds)
            ->join('repair_order_affected_task roat', 'task.id = roat.task_id')
            ->field("count(roat.task_id or null) as RepairNum")
            ->find();
        if (!empty($repairNumData['RepairNum'])) {
            $todayCountReturnData['RepairNum'] = intval($repairNumData['RepairNum']);
            return $todayCountReturnData;
        }
        return $todayCountReturnData;
    }

    /**
     * getTodayBaseQuery
     * @param $param
     * @param $userInfo
     * @param $startTime
     * @param $endTime
     * @param $projectMemberUserIdList
     * @param $delayTaskIds
     * @return mixed
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getTodayBaseQuery($param, $userInfo, $startTime, $endTime, $projectMemberUserIdList, $delayTaskIds)
    {
        $query = TaskModel::join('task_member', 'task_member.task_id = task.id', 'LEFT');
        if (!empty($userInfo['tenant']['id'])) {
            $query->where('task.tenant_id', $userInfo['tenant']['id']);
        }
        // 如果查询指定执行人
        if (!empty($param['executor'])) {
            $param['special_role_filter'] = 'artist';
        }
        if (!empty($param['task_step_type'])) {
            $query->whereIn('task.step_type', $param['task_step_type']);
        }
        // 特殊权限筛选
        if (!empty($param['special_role_filter'])) {
            $query = $this->getSpecialRoleFilterQuery($param, $userInfo, $projectMemberUserIdList, $query);
            if (empty($query)) {
                return [];
            }
        }
        if (!empty($param['todo_step_category_id'])) {
            $query->join("(select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category", 'r_step_category.task_id = task.id');
            $query->whereIn("r_step_category.link_id", $param['todo_step_category_id']);
        }
        if (!empty($param['project_id'])) {
            $query->where('task.project_id', $param['project_id']);
        }
        if (!empty($param['department_id'])) {
            $query->where('task.department_id', $param['department_id']);
        }
        if (!empty($param['executor_user_id_list'])) {
            $query->whereIn('task_member.partner', $param['executor_user_id_list']);
        }
        if (!empty($param['date_type']) && $param['date_type'] == 'delivery') {
            $query->whereBetweenTime('task.plan_delivery_time', $startTime, $endTime);
        } else if (!empty($param['date_type']) && $param['date_type'] == 'demand_add') { //新增
            $query->whereBetweenTime('task.created', $startTime, $endTime)->whereRaw("get_json_string(task.`json`,'task_is_append')", 'yes');
        } else if (!empty($param['date_type']) && $param['date_type'] == 'delay_created') { // 延期
            $query->whereIn('task.id', $delayTaskIds);
        } else if (!empty($param['date_type']) && $param['date_type'] == 'delivery_actually') { // 实际交付筛选
            $query->whereBetweenTime('task.delivery_time', $startTime, $endTime)->whereRaw("get_json_string(task.`json`,'task_status') = 7");
        } else {
            $query->whereBetweenTime('task.plan_end_time', $startTime, $endTime);
        }
        if (!empty($param['only_not_done'])) {
            $query->whereRaw("get_json_string(task.`json`, 'task_status') <> (select id from status where code = 'final')");
        }
        $query->whereRaw("task.step_type !='step_category'")->whereFieldRaw("get_json_string(`task`.`json`,'$.task_status')", '<>', 3);
        return $query;
    }

    /**
     * 特殊权限筛选where
     * @param $param
     * @param $userInfo
     * @param $projectMemberUserIdList
     * @param $query
     * @return array|mixed
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getSpecialRoleFilterQuery($param, $userInfo, $projectMemberUserIdList, $query)
    {
        if ($param['special_role_filter'] == 'artist') {
            $param['department_id'] = 0;
            $userId = UserService::getInstance()->getSaasUserIdByUnionId($userInfo['user']['id']);
            // 如果查询指定执行人
            if (!empty($param['executor'])) {
                $query->where('task_member.partner', $param['executor']);
            }
            // [我负责的任务]模块
            if (!empty($param['is_my_responsible'])) {
                if (!empty($param['executor_user_id_list']) && !empty($param['todo_step_category_id'])) {
                    $departmentManagerDepartmentIdList = [];
                    foreach ($userInfo['department_info'] ?? [] as $v) {
                        if ($v['role_code'] == 'headman' || $v['role_code'] == 'step_producer') {
                            $departmentManagerDepartmentIdList[] = $v['department_id'];
                        }
                    }
                    if (empty($departmentManagerDepartmentIdList)) {
                        return [];
                    }
                    $query->whereIn('task.department_id', $departmentManagerDepartmentIdList);
                }
            } else if (empty($param['executor'])) {
                $query->where('task_member.partner', $userId);
            }
        }
        if ($param['special_role_filter'] == 'headman') {
            $departmentManagerDepartmentIdList = [];
            foreach ($userInfo['department_info'] ?? [] as $v) {
                if ($v['role_code'] == 'headman' || $v['role_code'] == 'step_producer') {
                    $departmentManagerDepartmentIdList[] = $v['department_id'];
                }
            }
            if (empty($departmentManagerDepartmentIdList)) {
                return [];
            }
            $query->whereIn("task.department_id", $departmentManagerDepartmentIdList);
        }
        if ($param['special_role_filter'] == 'director') {
            $query->whereIn('task_member.partner', $projectMemberUserIdList);
        }
        return $query;
    }

    /**
     * 获取当前月计划代办任务进度
     * @param $projectId
     * @param $episodeId
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getPlanTodoTaskProgress($projectId, $episodeId): array
    {
        $projectShowStepCategoryList = OptionsService::getInstance()->getProjectShowStepCategoryListConfig();
        if (empty($projectShowStepCategoryList)) {
            return [];
        }
        $stepCategoryIds = [];
        foreach ($projectShowStepCategoryList as $projectShowStepCategoryItem) {
            $stepCategoryIds[] = $projectShowStepCategoryItem['id'];
        }
        // 获取该集下的任务统计
        $progressDataList = $this->getEpisodeTaskStepCategoryProgressData(null,
            sprintf("link_id in (%s)", join(',', $stepCategoryIds)),
            sprintf("link_id = %d", $episodeId),
            $projectId,
            true
        );
        if (empty($progressDataList)) {
            return [];
        }
        $scMaxEndTime = PlanModel::field('trt.link_id step_category_id,max(plan.end_time) end_time')
            ->join('task_relation_tag trt', "plan.task_id = trt.task_id and trt.link_module_code in ('step_category', 'show_step_category')")
            ->where('plan.project_id', $projectId)
            ->where('plan.scope', 'single')
            ->where('plan.type', 'step_category')
            ->where('plan.entity_id', $episodeId)
            ->group('trt.link_id')
            ->select();

        if (empty($scMaxEndTime->toArray())) {
            return [];
        }
        $scMaxEndTimeMap = [];
        foreach ($scMaxEndTime as $scMaxEndTimeItem) {
            $scMaxEndTimeMap[$scMaxEndTimeItem['step_category_id']] = intval($scMaxEndTimeItem['end_time'] ?? 0);
        }

        // 获取会议
        $meetingList = PlanModel::field('plan.id as id,plan.task_id as task_id,max(r_step_category.link_id) as step_category_id,task.name as name')
            ->join('task', 'plan.task_id = task.id')
            ->join('task_relation_tag r_step_category', "plan.task_id = r_step_category.task_id and r_step_category.link_module_code in ('step_category', 'show_step_category')")
            ->join('task_relation_tag r_episode', "task.id = r_episode.task_id and r_episode.link_module_code in ('episode')")
            ->where('plan.project_id', $projectId)
            ->where('r_episode.link_id', $episodeId)
            ->whereIn('r_step_category.link_id', $stepCategoryIds)
            ->where('plan.type', 'meeting')
            ->group('plan.id,plan.task_id,task.name')
            ->select();

        $meetingMap = [];
        if (!empty($meetingList->toArray())) {
            foreach ($meetingList as $meetingItem) {
                $meetingMap[$meetingItem['step_category_id']][] = $meetingItem;
            }
        }
        // 获取剧集计划封板时间
        $episodeStepCategorySealTimeList = EpisodeStepCategorySealTimeModel::field('id,entity_id,step_category_ids,seal_time')
            ->where('project_id', $projectId)
            ->where('entity_id', $episodeId)
            ->select()
            ->toArray();
        if (empty($episodeStepCategorySealTimeList)) {
            return [];
        }
        // 获取实际封板时间
        $episodeData = EntityModel::field('id,`json`')->where('id', $episodeId)->find()->toArray();
        $episodeJson = json_decode($episodeData['json'], true);

        $episodeIsSeal = !empty($episodeJson['episode_seal_time']) && $episodeJson['episode_seal_time'] > 0 && time() > $episodeJson['episode_seal_time'];

        // 剧集分类封板时间处理
        $episodeStepCategorySealTimeMap = [];
        foreach ($episodeStepCategorySealTimeList as $episodeStepCategorySealTimeItem) {
            foreach (explode(',', $episodeStepCategorySealTimeItem['step_category_ids']) as $stepCategoryId) {
                $currentStepCategoryId = intval($stepCategoryId);
                // 判定当前时间是否超过封板时间
                $episodeStepCategorySealTimeMap[$currentStepCategoryId] = time() > $episodeStepCategorySealTimeItem['seal_time'];
            }
        }
        $planListMap = [];
        foreach ($progressDataList as $progressDataListItem) {
            $planListMap[$progressDataListItem['step_category_id']] = $progressDataListItem;
        }

        // 根据大分类来合并
        $resIndexMap = [];

        $res = [];
        foreach ($projectShowStepCategoryList as $projectShowStepCategoryItem) {

            $currentIsSeal = false;
            // 成片code判定 ,此处code 暂时定死,将来可能会有改动
            if ($projectShowStepCategoryItem['module_code'] == 'color' || $projectShowStepCategoryItem['module_code'] == 'dub_in' || $projectShowStepCategoryItem['module_code'] = 'sound_mixing' || $projectShowStepCategoryItem['module_code'] == 'clip') {
                if ($episodeIsSeal) {
                    $currentIsSeal = true;
                }
            } else {
                if (!empty($episodeStepCategorySealTimeMap[$projectShowStepCategoryItem['id']])) {
                    $currentIsSeal = true;
                }
            }
            if (empty($resIndexMap[$projectShowStepCategoryItem['show_name']])) {
                $resIndexMap[$projectShowStepCategoryItem['show_name']] = count($res);
                $progressRes = [
                    'name' => $projectShowStepCategoryItem['show_name'],
                    'count' => intval($planListMap[$projectShowStepCategoryItem['id']]['count'] ?? 0),
                    'complete_count' => intval($planListMap[$projectShowStepCategoryItem['id']]['complete_count'] ?? 0),
                    'end_time' => intval($scMaxEndTimeMap[$projectShowStepCategoryItem['id']] ?? 0),
                    'step_category_ids' => [intval($projectShowStepCategoryItem['id'])],
                    'is_abnormal' => !empty($planListMap[$projectShowStepCategoryItem['id']]) && $planListMap[$projectShowStepCategoryItem['id']]['abnormal_count'] > 0,
                    'meeting_list' => $meetingMap[$projectShowStepCategoryItem['id']] ?? null
                ];
                $progressRes['is_seal'] = false;
                if ($currentIsSeal) {
                    $progressRes['is_seal'] = true;
                }
                $res[] = $progressRes;
            } else {

                if (!empty($planListMap[$projectShowStepCategoryItem['id']]['count'])) {
                    $res[$resIndexMap[$projectShowStepCategoryItem['show_name']]]['count'] += $planListMap[$projectShowStepCategoryItem['id']]['count'];
                }
                if (!empty($planListMap[$projectShowStepCategoryItem['id']]['complete_count'])) {
                    $res[$resIndexMap[$projectShowStepCategoryItem['show_name']]]['complete_count'] += $planListMap[$projectShowStepCategoryItem['id']]['complete_count'];
                }

                $res[$resIndexMap[$projectShowStepCategoryItem['show_name']]]['step_category_ids'][] = $projectShowStepCategoryItem['id'];

                if (empty($res[$resIndexMap[$projectShowStepCategoryItem['show_name']]]['is_abnormal']) && !empty($planListMap[$projectShowStepCategoryItem['id']]) && $planListMap[$projectShowStepCategoryItem['id']]['abnormal_count'] > 0) {
                    $res[$resIndexMap[$projectShowStepCategoryItem['show_name']]]['is_abnormal'] = $planListMap[$projectShowStepCategoryItem['id']]['abnormal_count'] > 0;
                }

                // 比较出最大的截至日期
                if ($scMaxEndTimeMap[$projectShowStepCategoryItem['id']] ?? 0 > $res[$resIndexMap[$projectShowStepCategoryItem['show_name']]]['end_time'] ?? 0) {
                    $res[$resIndexMap[$projectShowStepCategoryItem['show_name']]]['end_time'] = $scMaxEndTimeMap[$projectShowStepCategoryItem['id']];
                }
                // 合并会议列表
                if (!empty($meetingMap[$projectShowStepCategoryItem['id']])) {
                    $meetingMapValue = $meetingMap[$projectShowStepCategoryItem['id']];
                    foreach ($meetingMapValue as $meetingMapItem) {
                        $res[$resIndexMap[$projectShowStepCategoryItem['show_name']]]['meeting_list'][] = $meetingMapItem;
                    }
                }
                $res[$resIndexMap[$projectShowStepCategoryItem['show_name']]]['is_seal'] = false;
                if ($currentIsSeal) {
                    $res[$resIndexMap[$projectShowStepCategoryItem['show_name']]]['is_seal'] = true;
                }
            }
        }
        // 百分比计算
        foreach ($res as $resIndex => $resItem) {
            $currentProgress = 0;
            if (!empty($resItem['count'])) {
                $currentProgress = intval(floatval($resItem['complete_count']) / floatval($resItem['count']) * 100);
            }
            $resItem['progress'] = $currentProgress;
            $res[$resIndex] = $resItem;
        }
        return $res;
    }

    /**
     * 获取剧集任务环节进度数据
     * @param $whereSql
     * @param string $relationSpTagWhere
     * @param string $relationEpTagWhere
     * @param int $projectId
     * @param bool $isQueryAbnormal
     * @return array
     */
    public function getEpisodeTaskStepCategoryProgressData($whereSql, string $relationSpTagWhere, string $relationEpTagWhere, int $projectId, bool $isQueryAbnormal): array
    {
        $fieldList = [
            "r_step_category.link_id as step_category_id",
            "r_episode.link_id as episode_id",
            "count(r_step_category.link_id) as count",
            "count(if(get_json_string(task.`json`, 'task_status') = 7, 1, null)) as complete_count",
        ];
        // 是否查询异常
        if ($isQueryAbnormal) {
            $fieldList = array_merge($fieldList, [
                "count(if((task.assign_status = 'default' or task_work_order_temp.task_id > 0 or get_json_string(task.`json`, 'task_status') = 1),1, null)) as abnormal_count"
            ]);
        }

        $relationSpTag = sprintf("select * from task_relation_tag where project_id = %d and link_module_code in ('show_step_category','step_category')", $projectId);

        if (!empty($relationSpTagWhere)) {
            $relationSpTag .= " and " . $relationSpTagWhere;
        }

        $relationEpTag = sprintf("select * from task_relation_tag where project_id = %d and link_module_code = 'episode'", $projectId);

        if (!empty($relationEpTagWhere)) {
            $relationEpTag .= " and " . $relationEpTagWhere;
        }

        $db = TaskModel::field($fieldList)
            ->join(sprintf("(%s) r_step_category", $relationSpTag), 'task.id = r_step_category.task_id')
            ->join(sprintf("(%s) r_episode", $relationEpTag), 'task.id = r_episode.task_id');

        // 是否查询异常
        if ($isQueryAbnormal) {
            $db->join('(select task_id from task_work_order where status = 0 group by task_id) task_work_order_temp', 'task_work_order_temp.task_id = task.id', 'LEFT');
        }
        if (!empty($whereSql)) {
            $db->whereRaw($whereSql);
        }
        $closedStatusId = StatusService::getInstance()->byCode('closed')['id'];
        $res = $db->where('task.project_id', $projectId)
            ->whereRaw("task.step_type <> 'step_category'")
            ->whereFieldRaw("get_json_string(`task`.`json`,'task_status')", '<>', $closedStatusId)
            ->group('r_step_category.link_id,r_episode.link_id')
            ->select();
        if (empty($res->toArray())) {
            return [];
        }
        return $res->toArray();
    }

    /**
     * 总进度详情任务统计
     * @param $param
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function totalTaskCount($param): array
    {
        if (empty($param['type'])) {
            $param['type'] = 'step_category';
        }
        $currentNow = [
            'year' => date('Y', $param['is_now_month']),
            'month' => date('m', $param['is_now_month']),
        ];
        $entityIds = [];
        $res = [];
        $episodeTaskProgressList = [];
        $episodeTaskProgressMap = [];
        $yearMonthTaskNumMap = [];
        $directorYearMonthEntityMap = [];
        if (!empty($param['type']) && $param['type'] == 'step_category') {
            // 找出相关剧集
            $yearMonthEntityList = (new \app\model\PlanModel)
                ->field("from_unixtime(plan.end_time,'%Y%m') as year_month,from_unixtime(plan.end_time,'%Y') as year,from_unixtime(plan.end_time,'%m') as month,entity_id")
                ->join(sprintf("(select task_id from task_relation_tag where link_id in (%s) and link_module_code in ('show_step_category', 'step_category')) task_relation_tag", join(',', $param['category_id_list'])), 'task_relation_tag.task_id = plan.task_id')
                ->where('plan.scope', 'single')
                ->where('plan.type', 'step_category')
                ->whereRaw(sprintf("plan.start_time <= %d and plan.end_time >= %d and plan.project_id = %d", intval($param['end_time']), intval($param['start_time']), intval($param['project_id'])))
                ->group('year_month,year,month,entity_id')
                ->select();
            // 获取剧集id
            foreach ($yearMonthEntityList->toArray() ?? [] as $yearMonthEntityItem) {
                $year = $yearMonthEntityItem['year'];
                $month = $yearMonthEntityItem['month'];

                if (isset($yearMonthEntityMap[$year])) {
                    $monthMap = $yearMonthEntityMap[$year];
                    if (empty($monthMap[$month])) {
                        $yearMonthEntityMap[$year][$month] = [$yearMonthEntityItem['entity_id']];
                    } else {
                        $yearMonthEntityMap[$year][$month][] = $yearMonthEntityItem['entity_id'];
                    }
                } else {
                    $yearMonthEntityMap[$year] = [
                        $month => [$yearMonthEntityItem['entity_id']]
                    ];
                }
                $entityIds[] = $yearMonthEntityItem['entity_id'];
            }

            // 获取每个月的任务统计数量
            $totalTaskNumCount = $this->totalYearMonthTaskNumCount(
                [
                    'project_id' => $param['project_id'],
                    'episode_id' => $entityIds,
                    'step_category_id' => $param['category_id_list'],
                    'start_time' => $param['start_time'],
                    'end_time' => $param['end_time'],
                ]
            );

            foreach ($totalTaskNumCount as $totalTaskNumCountItem) {
                $year = $totalTaskNumCountItem['year'];
                $month = $totalTaskNumCountItem['month'];
                if (!empty($yearMonthTaskNumMap[$year])) {
                    $yearMonth = $yearMonthTaskNumMap[$year];
                    if (empty($yearMonth[$month])) {
                        $yearMonthTaskNumMap[$year][$month] = $totalTaskNumCountItem['count'];
                    }
                } else {
                    $yearMonthTaskNumMap[$year] = [
                        $month => $totalTaskNumCountItem['count']
                    ];
                }
            }
            // 获取每月每集的任务统计
            $taskProgressRes = $this->totalYearMonthEpisodeTaskProgress([
                'step_category_id' => $param['category_id_list'],
                'episode_id' => $entityIds,
                'project_id' => $param['project_id']
            ]);

            // 根据集来合并taskProgressRes统计
            $tmpTaskProgressResItemCount = 0;
            $tmpTaskProgressResItemCompleteCount = 0;
            foreach ($taskProgressRes as $taskProgressResItem) {
                if (!isset($episodeTaskProgressMap[$taskProgressResItem['episode_id']])) {
                    $episodeTaskProgressMap[$taskProgressResItem['episode_id']] = count($episodeTaskProgressList);
                    $episodeTaskProgressList[] = [
                        'count' => $taskProgressResItem['count'],
                        'completeCount' => $taskProgressResItem['complete_count'],
                    ];
                } else {
                    $tmpTaskProgressResItemCount += $taskProgressResItem['count'];
                    $tmpTaskProgressResItemCompleteCount += $taskProgressResItem['complete_count'];
                    $episodeTaskProgressIndex = $episodeTaskProgressMap[$taskProgressResItem['episode_id']];
                    $episodeTaskProgressList[$episodeTaskProgressIndex]['count'] = $tmpTaskProgressResItemCount;
                    $episodeTaskProgressList[$episodeTaskProgressIndex]['completeCount'] = $tmpTaskProgressResItemCompleteCount;
                }
            }
        } else {
            // 导演统计
            $userIds = ProjectMemberModel::field('user_id')
                ->where('project_id', $param['project_id'])
                ->where('role_code', 'director')
                ->column('user_id');
            $userIds = array_unique($userIds);

            // 获取到剧集
            $yearMonthEntityList = PlanModel::field("from_unixtime(plan.end_time,'%Y%m') as year_month,from_unixtime(plan.end_time,'%Y') as year,from_unixtime(plan.end_time,'%m') as month,entity_id")
                ->join(sprintf("(select task_id from task_member where partner in (%s)) task_member", join(',', $userIds)), 'plan.task_id = task_member.task_id')
                ->join(sprintf("(select link_id,task_id from task_relation_tag where project_id=%d and link_module_code in ('episode')) r_episode", $param['project_id']), 'plan.task_id = r_episode.task_id')
                ->whereRaw(sprintf("plan.project_id = %d and plan.type in ('meeting','review') and plan.end_time >= %d and plan.end_time <=%d", $param['project_id'], $param['start_time'], $param['end_time']))
                ->select();

            foreach ($yearMonthEntityList as $yearMonthEntityItem) {
                $year = $yearMonthEntityItem['year'];
                $month = $yearMonthEntityItem['month'];
                if (isset($yearMonthEntityMap[$year])) {
                    if (empty($yearMonth[$month])) {
                        $yearMonthTaskNumMap[$year][$month] = $yearMonthEntityItem['entity_id'];
                    }
                } else {
                    $yearMonthTaskNumMap[$year] = [
                        $month => [$yearMonthEntityItem['entity_id']]
                    ];
                }
                $entityIds[] = $yearMonthEntityItem['entity_id'];
            }

            // 查询任务总数
            $directorYearMonthTaskNum = $this->totalDirectorYearMonthTaskNumCount($userIds, $param['project_id'], $entityIds, $param['start_time'], $param['end_time']);
            foreach ($directorYearMonthTaskNum as $directorYearMonthTaskNumItem) {
                $year = $directorYearMonthTaskNumItem['year'];
                $month = $directorYearMonthTaskNumItem['month'];
                if (isset($yearMonthTaskNumMap[$year])) {
                    $monthValue = $yearMonthTaskNumMap[$year];
                    if (empty($monthValue[$month])) {
                        $yearMonthTaskNumMap[$year][$month] = $directorYearMonthTaskNumItem['count'];
                    }
                } else {
                    $yearMonthTaskNumMap[$year] = [
                        $month => $directorYearMonthTaskNumItem['count'],
                    ];
                }
            }
            // 统计导演每月集任务进度
            $directorYearMonthEntityList = $this->totalDirectorYearMonthEpisodeTaskProgress($userIds, $param['project_id'], $entityIds, $param['start_time'], $param['end_time']);
            // 根据集来合并taskProgress map
            foreach ($directorYearMonthEntityList as $directorYearMonthEntityItem) {
                $year = $directorYearMonthEntityItem['year'];
                $month = $directorYearMonthEntityItem['month'];
                if (isset($directorYearMonthEntityMap[$year])) {
                    $monthMap = $directorYearMonthEntityMap[$year];
                    if (empty($monthMap[$month])) {
                        $directorYearMonthEntityMap[$year][$month] = [
                            $directorYearMonthEntityItem
                        ];
                    } else {
                        $directorYearMonthEntityMap[$year][$month][] = $directorYearMonthEntityItem;
                    }
                } else {
                    $directorYearMonthEntityMap[$year] = [
                        'year_month' => 0,
                        'year' => 0,
                        'month' => $directorYearMonthEntityItem,
                        'episode_id' => 0,
                        'count' => 0,
                        'complete_count' => 0,
                    ];
                }
            }
        }

        // 查询剧集信息
        $episodeList = EntityModel::field('id,name')->whereIn('id', join(',', $entityIds))->select();
        $episodeMap = [];
        if (!empty($episodeList->toArray())) {
            $episodeMap = array_column($episodeList->toArray(), null, 'id');
        }

        // 年月 填补 剧集数据 任务数量
        $yearMonthList = get_month_list($param['start_time'], $param['end_time']);
        foreach ($yearMonthList as $year => $monthList) {
            $countMonthList = [];
            foreach ($monthList as $month) {
                // 剧集列表
                $episodeNameList = null;
                if ($param['type'] == 'step_category') {
                    if (isset($yearMonthEntityMap[$year][$month])) {
                        $episodeIds = $yearMonthEntityMap[$year][$month];
                        foreach ($episodeIds as $episodeId) {
                            if (isset($episodeTaskProgressMap[$episodeId])) {
                                $taskProgressValue = $episodeTaskProgressMap[$episodeId];
                                $progress = $episodeTaskProgressList[$taskProgressValue];
                                $episodeNameList[] = [
                                    'episode_id' => intval($episodeId),
                                    'episode_name' => $episodeMap[$episodeId]['name'],
                                    'progress' => intval((floatval($progress['completeCount']) / floatval($progress['count'])) * 100),
                                    'final_task_num' => intval($progress['completeCount'] ?? 0),
                                    'total_task_num' => intval($progress['count'] ?? 0),
                                ];
                            }
                        }
                    }
                } else {
                    if (isset($directorYearMonthEntityMap[$year][$month])) {
                        $directorYearMonthEntityItem = $directorYearMonthEntityMap[$year][$month];
                        foreach ($directorYearMonthEntityItem as $directorYearMonthEntityValue) {
                            $episodeNameList[] = [
                                'episode_id' => intval($directorYearMonthEntityValue['episode_id']),
                                'episode_name' => $episodeMap[$directorYearMonthEntityValue['episode_id']]['name'],
                                'final_task_num' => intval($directorYearMonthEntityValue['complete_count']),
                                'progress' => intval((floatval($directorYearMonthEntityValue['complete_count']) / $directorYearMonthEntityValue['count']) * 100),
                                'total_task_num' => intval($directorYearMonthEntityValue['count']),
                            ];
                        }
                    }
                }
                $isNowMonth = 'no';
                if ($currentNow['year'] == $year && $currentNow['month'] == $month) {
                    $isNowMonth = 'yes';
                }
                $countMonthList[] = [
                    'is_now_month' => $isNowMonth,
                    'month_name' => intval($month),
                    'task_num' => intval($yearMonthTaskNumMap[$year][$month] ?? 0),
                    'episode_name_list' => $episodeNameList,
                ];
            }
            $res[] = [
                'year' => $year,
                'month' => $countMonthList,
            ];

        }
        return $res;
    }


    /**
     * 统计导演每月集任务进度
     * @param array $userIds
     * @param int $projectId
     * @param array $entityIds
     * @param int $startTime
     * @param int $endTIme
     * @return array
     */
    public function totalDirectorYearMonthEpisodeTaskProgress(array $userIds, int $projectId, array $entityIds, int $startTime, int $endTIme): array
    {
        if (empty($entityIds)) {
            return [];
        }
        $field = [
            "from_unixtime(plan.end_time,'%Y%m') AS year_month",
            "from_unixtime(plan.end_time,'%Y') as year",
            "from_unixtime(plan.end_time,'%m') as month",
            'r_episode.link_id as episode_id',
            "count(task.id) as count",
            "count(if(get_json_string(`task`.`json`,'task_status') = 7, 1, null)) as complete_count"
        ];
        $res = (new \app\model\PlanModel)->field($field)
            ->join(sprintf("(select task_id from task_member where partner in (%s)) task_member", join(',', $userIds)), 'plan.task_id = task_member.task_id')
            ->join(sprintf("(select link_id,task_id from task_relation_tag where project_id=%d and link_id in (%s) and link_module_code in ('episode')) r_episode", $projectId, join(',', $entityIds)), 'plan.task_id = r_episode.task_id')
            ->join('task', 'task.id = plan.task_id')
            ->whereRaw(sprintf("plan.project_id = %d and plan.type in ('meeting','review') and plan.end_time >= %d and plan.end_time <=%d", $projectId, $startTime, $endTIme))
            ->group('year_month,year,month,r_episode.link_id')
            ->select();
        if (empty($res->toArray())) {
            return [];
        }
        return $res->toArray();
    }


    /**
     * 统计导演任务数量
     * @param array $userIds
     * @param int $projectId
     * @param array $entityIds
     * @param int $startTime
     * @param int $endTIme
     * @return array
     */
    public function totalDirectorYearMonthTaskNumCount(array $userIds, int $projectId, array $entityIds, int $startTime, int $endTIme): array
    {
        if (empty($entityIds)) {
            return [];
        }
        $field = [
            "from_unixtime(plan.end_time,'%Y%m') AS year_month",
            "from_unixtime(plan.end_time,'%Y') as year",
            "from_unixtime(plan.end_time,'%m') as month"
        ];
        $res = PlanModel::field($field)
            ->join(sprintf("(select task_id from task_member where partner in (%s)) task_member", join(',', $userIds)), 'plan.task_id = task_member.task_id')
            ->join(sprintf("(select link_id,task_id from task_relation_tag where project_id=%d and link_id in (%s) and link_module_code in ('episode')) r_episode", $projectId, join(',', $entityIds)), 'plan.task_id = r_episode.task_id')
            ->whereRaw(sprintf("plan.project_id = %d and plan.type in ('meeting','review') and plan.end_time >= %d and plan.end_time <=%d", $projectId, $startTime, $endTIme))
            ->group('year_month,year,month')
            ->select();
        if (!empty($res->toArray())) {
            return [];
        }
        return $res->toArray();
    }


    /**
     * totalTaskCount
     * @param $param
     * @return array
     */
    public function totalYearMonthEpisodeTaskProgress($param): array
    {
        $stepCategoryStr = [];
        $stepCategoryMap = [];
        $episodeIdStr = [];

        foreach ($param['step_category_id'] ?? [] as $stepCategoryId) {
            if (empty($stepCategoryMap[$stepCategoryId])) {
                $stepCategoryMap[$stepCategoryId] = true;
                $stepCategoryStr[] = $stepCategoryId;
            }
        }
        foreach ($param['episode_id'] ?? [] as $episodeId) {
            if (empty($episodeIdMap[$episodeId])) {
                $episodeIdMap[$episodeId] = true;
                $episodeIdStr[] = $episodeId;
            }
        }
        $stepCategoryWhereStr = '';
        if (!empty($stepCategoryStr)) {
            $stepCategoryWhereStr = sprintf("link_id in (%s)", join(',', $stepCategoryStr));
        }

        $episodeIdWhereStr = '';
        if (!empty($episodeIdStr)) {
            $episodeIdWhereStr = sprintf("link_id in (%s)", join(',', $episodeIdStr));
        }

        $progressData = $this->getEpisodeTaskStepCategoryProgressData(
            null,
            $stepCategoryWhereStr,
            $episodeIdWhereStr,
            $param['project_id'],
            false
        );
        $res = [];
        foreach ($progressData as $progressDataItem) {
            $res[] = [
                'episode_id' => $progressDataItem['episode_id'] ?? 0,
                'step_category_id' => $progressDataItem['step_category_id'] ?? 0,
                'count' => $progressDataItem['count'] ?? 0,
                'complete_count' => $progressDataItem['complete_count'] ?? 0,
            ];
        }
        return $res;
    }


    /**
     * 统计任务数量
     * @param $param
     * @return array
     */
    public function totalYearMonthTaskNumCount($param): array
    {
        if (empty($param['episode_id'])) {
            return [];
        }
        $res = (new \app\model\PlanModel)
            ->field("from_unixtime(plan.end_time,'%Y%m') AS year_month,from_unixtime(plan.end_time,'%Y') as year,from_unixtime(plan.end_time,'%m') as month, count(distinct tr_step_category.task_id) as count")
            ->join(sprintf("(select task_id from task_relation_tag where project_id=%d and link_module_code in ('show_step_category', 'step_category') and link_id in (%s)) as pr_step_category", $param['project_id'], join(',', $param['step_category_id'])), 'plan.task_id = pr_step_category.task_id')
            ->join(sprintf("(select link_id,task_id from task_relation_tag where project_id=%d and link_module_code in ('episode') and link_id in (%s)) as tr_episode", $param['project_id'], join(',', $param['episode_id'])), 'plan.entity_id = tr_episode.link_id')
            ->join(sprintf("(select task_id from task_relation_tag where project_id=%d and link_module_code in ('show_step_category', 'step_category')  and link_id in (%s)) as tr_step_category", $param['project_id'], join(',', $param['step_category_id'])), 'tr_episode.task_id = tr_step_category.task_id')
            ->whereRaw(sprintf("plan.scope = 'single' and plan.type = 'step_category' and plan.project_id = %d and plan.start_time <= %d and plan.end_time>= %d", $param['project_id'], $param['end_time'], $param['start_time']))
            ->group('year_month,year,month')
            ->select();
        if (empty($res->toArray())) {
            return [];
        }
        return $res->toArray();
    }

    /**
     * 获取当前月计划代办任务进度
     * @param Request $request
     * @param int $projectId
     * @param array $stepCategoryIds
     * @param int $episodeId
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function episodeTaskCount(Request $request, int $projectId, array $stepCategoryIds, int $episodeId): array
    {
        $res = [];
        // 获取父级分类,通过父级分类,拿取module_code
        $stepCategoryId = $stepCategoryIds[array_key_first($stepCategoryIds)];
        $stepCategory = StepCategoryModel::where(['id' => $stepCategoryId])->find();

        $moduleId = 0;
        if (!empty($stepCategory['parent_id'])) {
            $moduleId = StepCategoryModel::where(['id' => $stepCategory['parent_id']])->column('entity_id');
            $moduleId = (int)array_shift($moduleId);
        } else {
            $moduleId = $stepCategory['entity_id'];
        }
        $moduleCode = ModuleService::getInstance()->byId($moduleId)['code'];

        switch ($moduleCode) {
            case 'asset':
            case 'level':
            case 'design':
                $moduleCode = 'asset';
                break;
            case 'shot':
            case 'storyboard':
                $moduleCode = 'shot';
                break;
        }
        // 如果不在资产线和镜头线中,则不查出来
        if (in_array($moduleCode, ['asset', 'shot'])) {
            $scContainer = self::getEpisodeStepCategoryTaskProgressCount($projectId, $stepCategoryIds, $episodeId);
            $res[] = array_merge(['module_name' => $moduleCode, 'name' => 'entity'], $scContainer);
        }
        $ettContainer = self::episodeTodoTaskCount($projectId, $stepCategoryIds, $episodeId);
        $res[] = array_merge(['module_name' => 'todo', 'name' => 'todo'], $ettContainer);

        return result_array_construct([
            'module_name' => 'strval',
            'name' => 'strval',
            'count' => 'intval',
            'finish_count' => 'intval',
            'abnormal_count' => 'intval',
        ], $res);
    }

    /**
     * @param int $projectId
     * @param array $stepCategoryIds
     * @param int $episodeId
     * @return int[]
     */
    private function getEpisodeStepCategoryTaskProgressCount(int $projectId, array $stepCategoryIds, int $episodeId): array
    {
        $res = [
            'count' => 0,
            'finish_count' => 0,
            'abnormal_count' => 0,
        ];
        $episodeCode = EntityModel::where('id', $episodeId)->column('code');
        if (empty($episodeCode)) {
            return $res;
        }
        $episodeCode = array_shift($episodeCode);

        $current = time();
        $stoppedStatusSql = StatusService::getInstance()->byCodeMap(['closed', 'final'], false, 'id');
        $healthyStatusSql = StatusService::getInstance()->byCodeMap(['closed', 'final', 'ready_to_publish'], false, 'id');
        $abnormalStatusIdSql = StatusService::getInstance()->byCodeMap(['not_started', 'ready_to_start', 'wait_assemble'], false, 'id');
        $abnormalConditionSql = 'task_work_order_temp.task_id is not null '
            . ' or ('
//            . ' task.step_type = "step"'
//            . ' and (task.assign_status = "default" or get_json_string(`task`.`json`, "$.task_status") in (' . $abnormalStatusIdSql . ')) and '
            . ' task.plan_end_time between 1 and ' . $current
            . ' and get_json_string(`task`.`json`, "$.task_status") not in (' . $stoppedStatusSql . ') '
            . ' )';
        $subDbr = TaskWorkOrderModel::where('status', 0)->field('task_id')->group('task_id')->buildSql(true);
        $count = EntityModel::join('task', 'task.entity_id = entity.id', 'left')
            ->join([$subDbr => 'task_work_order_temp'], 'task_work_order_temp.task_id = task.id', 'left')
            ->where([
                'entity.project_id' => $projectId,
                'entity.initial_episode_code' => $episodeCode,
                'task.step_type' => 'step',
            ])
            ->whereIn('task.step_category_id', $stepCategoryIds)
            ->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', '<>', 3)
            ->fieldRaw(' count(distinct task.id) as count, '
                . ' count(distinct if(get_json_string(`task`.`json`, "$.task_status") not in (' . $healthyStatusSql . '), task.id, null )) as finish, '
                . ' count(distinct if(' . $abnormalConditionSql . ', task.id, null)) as abnormal ')->select()->toArray();
        $count = array_shift($count);

        //总数
        $res['count'] = intval($count['count']);
        //完成的总数
        $res['finish_count'] = intval($count['count']) - intval($count['finish']);
        //异常
        $res['abnormal_count'] = intval($count['abnormal']);

        return $res;
    }

    /**
     * 集代办任务
     * @param $projectId
     * @param $stepCategoryIds
     * @param $episodeId
     * @return int[]
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    private function episodeTodoTaskCount($projectId, $stepCategoryIds, $episodeId): array
    {
        $res = [
            'count' => 0,
            'finish_count' => 0,
            'abnormal_count' => 0,
        ];

        // 获取父级分类,通过父级分类,拿取module_code
        $stepCategoryId = $stepCategoryIds[array_key_first($stepCategoryIds)];
        $stepCategory = StepCategoryModel::where(['id' => $stepCategoryId])->find();

        $moduleId = 0;
        if (!empty($stepCategory['parent_id'])) {
            $moduleId = StepCategoryModel::where(['id' => $stepCategory['parent_id']])->column('entity_id');
            $moduleId = (int)array_shift($moduleId);
        } else {
            $moduleId = $stepCategory['entity_id'];
        }
        $moduleCode = ModuleService::getInstance()->byId($moduleId)['code'];

        $epTrtJoin = TaskRelationTagModel::where('link_module_code', 'episode')->buildSql();
        $scTrtJoin = TaskRelationTagModel::whereIn('link_module_code', ["step_category", "show_step_category"])->buildSql();
        $taskDbr = TaskModel::join([$epTrtJoin => 'ep_trt'], 'ep_trt.task_id = task.id')
            ->join([$scTrtJoin => 'sc_trt'], 'sc_trt.task_id = task.id')
            ->where([
                'task.project_id' => $projectId,
                'ep_trt.link_id' => $episodeId,
            ])
            ->whereIn('sc_trt.link_id', $stepCategoryIds)
            ->whereIn('task.step_type', ['to_do', 'work_order_to_do', 'entity_to_do'])
            ->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', '<>', 3);
        $count = $taskDbr->fieldRaw(' count(distinct task.id) as count, '
            . ' count(distinct if(get_json_string(`task`.`json`, "$.task_status") = 7, task.id, null)) as finish, '
            . ' count(distinct if(get_json_string(`task`.`json`, "$.task_status") <> 7 AND task.plan_end_time < ' . time() . ', task.id, null)) as abnormal ')
            ->select()->toArray();
        $count = array_shift($count);
        $res['count'] = intval($count['count']);
        $res['finish_count'] = intval($count['finish']);
        $res['abnormal_count'] = intval($count['abnormal']);
        return $res;
    }

    /**
     * 今日统计
     * @param $param
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function progressCount($param): array
    {
        // 环节进度统计
        $res = $this->getStepCategoryProgressCount($param['project_id']);
        // 导演进度统计
        $res[] = $this->getDirectorProgressCount($param['project_id']);
        return $res;
    }

    /**
     * 获取环节进度统计
     * @param $projectId
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getStepCategoryProgressCount($projectId): array
    {
        $projectShowStepCategoryList = OptionsService::getInstance()->getProjectShowStepCategoryListConfig();
        if (empty($projectShowStepCategoryList)) {
            return [];
        }
        $showStepCategoryIdList = array_column($projectShowStepCategoryList, 'id');
        // 查询异常情况
        $totalTodayAbnormalList = $this->getTotalTodayAbnormalData($projectId, $showStepCategoryIdList);
        $totalTodayAbnormalMapData = [];
        foreach ($totalTodayAbnormalList as $abnormalDatum) {
            $totalTodayAbnormalMapData[sprintf('%d-%s', $abnormalDatum['step_category_id'], 'total')] = $abnormalDatum['total_abnormal_number'] > 0;
            $totalTodayAbnormalMapData[sprintf('%d-%s', $abnormalDatum['step_category_id'], 'today')] = $abnormalDatum['today_abnormal_number'] > 0;
        }

        $todayStartTime = strtotime(date('Y-m-d', time()));
        $todayEndTime = strtotime(date('Y-m-d 23:59:59', time()));
        $stepCategoryCountList = $this->getPlanEndTimeStepCategoryCount($todayStartTime, $todayEndTime, $projectId, $showStepCategoryIdList);
        $stepCategoryCountMap = array_column($stepCategoryCountList, null, 'step_category_id');
        $getMonthTime = get_month_time(date('Y-m-d', time()));
        $pqRes = (new \app\model\PlanModel)->cache('pqRes', 5)
            ->field("r_episode.link_id as episode_id,r_step_category.link_id as step_category_id")
            ->join("(select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category", 'r_step_category.task_id = plan.task_id', 'LEFT')
            ->join("(select * from task_relation_tag where link_module_code in ('episode')) r_episode", 'r_episode.task_id = plan.task_id', 'LEFT')
            ->where('plan.scope', 'single')
            ->where('plan.type', 'step_category')
            ->where('plan.project_id', $projectId)
            ->whereBetween('plan.end_time', [$getMonthTime['sdate'], $getMonthTime['edate']])
            ->group('r_episode.link_id,r_step_category.link_id')
            ->select();
        if (empty($pqRes->toArray())) {
            return [];
        }
        $pqRes = $pqRes->toArray();
        $episodeStepCategoryCountList = $this->getEpisodeStepCategoryCountList($pqRes, $projectId);
        $episodeStepCategoryCountMap = [];
        foreach ($episodeStepCategoryCountList as $count) {
            $episodeStepCategoryCountMap[sprintf("%d#%d", $count['episode_id'], $count['step_category_id'])] = $count;
        }
        $monthEpisodeAbnormalData = $this->getMonthEpisodeAbnormalData($pqRes, $projectId);
        $monthEpisodeAbnormalMapData = [];
        foreach ($monthEpisodeAbnormalData ?? [] as $datum) {
            $monthEpisodeAbnormalMapData[sprintf("%d-%d", $datum['episode_id'], $datum['step_category_id'])] = $datum['abnormal_number'];
        }
        $episodeList = (new \app\model\EntityModel)->cache('episodeList', 5)
            ->field('id,name,code')
            ->where('project_id', $projectId)
            ->where('module_id', ModuleService::getInstance()->byCode('episode')['id'])
            ->order('id asc')
            ->select();
        if (empty($episodeList->toArray())) {
            return [];
        }
        $episodeList = $episodeList->toArray();
        return $this->execStepCategoryProgressRes($projectShowStepCategoryList, $stepCategoryCountMap, $episodeList, $episodeStepCategoryCountMap, $monthEpisodeAbnormalMapData, $totalTodayAbnormalMapData);
    }

    /**
     * 获取计划结束时间环节分类统计
     * @param $todayStartTime
     * @param $todayEndTime
     * @param $projectId
     * @param $showStepCategoryIdList
     * @return array
     */
    public function getPlanEndTimeStepCategoryCount($todayStartTime, $todayEndTime, $projectId, $showStepCategoryIdList): array
    {
        $finalStatusId = StatusService::getInstance()->byCode('final')['id'];
        $closedStatusId = StatusService::getInstance()->byCode('closed')['id'];
        $stepCategoryCountField = [
            'trt.link_id as step_category_id',
            'count(task.id) total_task',
            "count(get_json_string(`task`.`json`,'task_status') = $finalStatusId or null) done_task",
            sprintf("count(((task.plan_end_time between %d and %d) or (task.plan_end_time > 0 and task.plan_end_time < %d and task.end_time = 0)) or null) today_total_task", $todayStartTime, $todayEndTime, $todayStartTime),
            sprintf("count((task.plan_end_time between %d and %d and get_json_string(`task`.`json`,'task_status') = $finalStatusId) or null) today_done_task", $todayStartTime, $todayEndTime)
        ];
        $join = sprintf("(select * from task_relation_tag where project_id =%d and link_module_code in ('step_category', 'show_step_category') and link_id in (%s)) trt", $projectId, join(',', $showStepCategoryIdList));
        $stepCategoryCountList = (new \app\model\TaskModel)->cache('getPlanEndTimeStepCategoryCount', '5')
            ->field($stepCategoryCountField)
            ->join($join, 'task.id = trt.task_id')
            ->where('task.project_id', $projectId)
            ->whereFieldRaw('task.entity_module_id', '<>', ModuleService::getInstance()->byCode('storyboard')['id'])
            ->whereFieldRaw("get_json_string(`task`.`json`,'task_status')", '<>', $closedStatusId)
            ->group('trt.link_id')
            ->select();
        if (empty($stepCategoryCountList->toArray())) {
            return [];
        }
        return $stepCategoryCountList->toArray();
    }

    /**
     * 组织环节进度返回数据
     * @param $projectShowStepCategoryList
     * @param $stepCategoryCountMap
     * @param $episodeList
     * @param $episodeStepCategoryCountMap
     * @param $monthEpisodeAbnormalMapData
     * @param $totalTodayAbnormalMapData
     * @return array
     */
    public function execStepCategoryProgressRes($projectShowStepCategoryList, $stepCategoryCountMap, $episodeList, $episodeStepCategoryCountMap, $monthEpisodeAbnormalMapData, $totalTodayAbnormalMapData): array
    {
        $stepCategoryProgressRes = [];
        $resIndex = 0;
        foreach ($projectShowStepCategoryList as $v) {
            $TotalProgressTotal = 0;
            $TotalProgressDone = 0;
            $TodayProgressTotal = 0;
            $TodayProgressDone = 0;
            if (!empty($stepCategoryCountMap[$v['id']])) {
                $TotalProgressTotal = intval($stepCategoryCountMap[$v['id']]['total_task']);
                $TotalProgressDone = intval($stepCategoryCountMap[$v['id']]['done_task']);
                $TodayProgressTotal = intval($stepCategoryCountMap[$v['id']]['today_total_task']);
                $TodayProgressDone = intval($stepCategoryCountMap[$v['id']]['today_done_task']);
            }
            if (!empty($resNameMap[$v['show_name']])) {
                $resNameMap[$v['show_name']]['category_id_list'][] = $v['id'];
                $resNameMap[$v['show_name']]['total_progress_total'] += $TotalProgressTotal;
                $resNameMap[$v['show_name']]['total_progress_done'] += $TotalProgressDone;
                $resNameMap[$v['show_name']]['today_progress_total'] += $TodayProgressTotal;
                $resNameMap[$v['show_name']]['today_progress_done'] += $TodayProgressDone;
                if (!isset($moduleCodeMap[$v['show_name'] . $v['module_code']])) {
                    $resNameMap[$v['show_name']]['module_code_list'][] = $v['module_code'];
                    $moduleCodeMap[$v['show_name'] . $v['module_code']] = true;
                }
            } else {
                $moduleCodeMap[$v['show_name'] . $v['module_code']] = true;
                $stepCategoryProgressRes[] = [
                    'category_id_list' => [$v['id']],
                    'module_code_list' => [$v['module_code']],
                    'category_name' => $v['show_name'],
                    'total_progress_total' => $TotalProgressTotal,
                    'total_progress_abnormal' => false,
                    'total_progress_done' => $TotalProgressDone,
                    'today_progress_abnormal' => false,
                    'today_progress_total' => $TodayProgressTotal,
                    'today_progress_done' => $TodayProgressDone,
                    'current_month_should_done' => [],
                    'type' => 'step_category'
                ];
                $resNameMap[$v['show_name']] = &$stepCategoryProgressRes[$resIndex];
                $resIndex += 1;
            }
            if (!empty($totalTodayAbnormalMapData[sprintf('%d-%s', $v['id'], 'today')])) {
                $resNameMap[$v['show_name']]['today_progress_abnormal'] = true;
            }
            if (!empty($totalTodayAbnormalMapData[sprintf("%d-%s", $v['id'], 'total')])) {
                $resNameMap[$v['show_name']]['total_progress_abnormal'] = true;
            }
            $currentMonthDoneEpisodeIndex = count($resNameMap[$v['show_name']]['current_month_should_done']) - 1;
            foreach ($episodeList as $entity) {
                $uniqueKey = sprintf('%d#%d', $entity['id'], $v['id']);
                if (isset($episodeStepCategoryCountMap[$uniqueKey])) {
                    $stepCategoryShouldDoneEpisodeIndexString = sprintf("%s#%s", $v['show_name'], $entity['name']);
                    if (isset($stepCategoryShouldDoneEpisodeMap[$stepCategoryShouldDoneEpisodeIndexString])) {
                        $stepCategoryShouldDoneEpisodeIndex = $stepCategoryShouldDoneEpisodeMap[$stepCategoryShouldDoneEpisodeIndexString];
                        $resNameMap[$v['show_name']]['current_month_should_done'][$stepCategoryShouldDoneEpisodeIndex]['total'] += $episodeStepCategoryCountMap[$uniqueKey]['total_count'];
                        $resNameMap[$v['show_name']]['current_month_should_done'][$stepCategoryShouldDoneEpisodeIndex]['done'] += $episodeStepCategoryCountMap[$uniqueKey]['done_count'];
                    } else {
                        $resNameMap[$v['show_name']]['current_month_should_done'][] = [
                            'episode_id' => intval($entity['id']),
                            'episode_name' => $entity['name'],
                            'progress' => 0,
                            'total' => intval($episodeStepCategoryCountMap[$uniqueKey]['total_count']),
                            'done' => intval($episodeStepCategoryCountMap[$uniqueKey]['done_count']),
                        ];
                        $currentMonthDoneEpisodeIndex += 1;
                        $stepCategoryShouldDoneEpisodeMap[$stepCategoryShouldDoneEpisodeIndexString] = $currentMonthDoneEpisodeIndex;
                    }
                    $monthEpisodeAbnormalIndex = sprintf("%d-%d", $entity['id'], $v['id']);
                    if (!empty($monthEpisodeAbnormalMapData[$monthEpisodeAbnormalIndex])) {
                        $resNameMap[$v['show_name']]['current_month_should_done'][$currentMonthDoneEpisodeIndex]['abnormal'] = true;
                    }
                }
            }
        }
        return $stepCategoryProgressRes;
    }

    /**
     * 获取导演进度统计
     * @param $projectId
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getDirectorProgressCount($projectId): array
    {
        // 查询所有用户是导演的角色
        $directorUserIds = ProjectMemberService::getInstance()->getDirectorUserIds($projectId);
        if (empty($directorUserIds)) {
            return ['type' => 'role', 'category_id_list' => [], 'category_name' => '导演', 'total_progress_total' => 0, 'total_progress_done' => 0, 'today_progress_total' => 0, 'today_progress_done' => 0, 'current_month_should_done' => []];
        }
        $closedStatusId = StatusService::getInstance()->byCode('closed')['id'];
        $finalStatusId = StatusService::getInstance()->byCode('final')['id'];
        // 查询执行人是导演的任务
        $directorStructList = (new \app\model\TaskModel)->field("count(distinct task.id) total_count,count(get_json_string(`task`.`json`,'task_status') = $finalStatusId or null) done_count")
            ->where('p.project_id', $projectId)
            ->whereIn('p.type', ['meeting', 'review'])
            ->where('task.step_type', '<>', 'step')
            ->whereFieldRaw("get_json_string(`task`.`json`,'task_status')", '<>', $closedStatusId)
            ->whereIn('tm.partner', $directorUserIds)
            ->join('task_member tm', 'task.id = tm.task_id')
            ->join('plan p', 'task.id = p.task_id')
            ->select();
        if (empty($directorStructList->toArray())) {
            return [];
        }
        $directorStructList = $directorStructList->toArray();
        $tmpTotalProgressTotal = 0;
        $tmpTotalProgressDone = 0;
        foreach ($directorStructList as $v) {
            $tmpTotalProgressTotal += intval($v['total_count']);
            $tmpTotalProgressDone += intval($v['done_count']);
        }
        // 今日总数
        $todayStartTime = strtotime(date('Y-m-d', time()));
        $todayEndTime = strtotime(date('Y-m-d 23:59:59', time()));
        $tmpTodayProgressTotal = (new \app\model\TaskModel)->field("count(distinct task.id) done_count")
            ->join('task_member tm', 'task.id = tm.task_id')->join('plan p', 'task.id = p.task_id')
            ->where('p.project_id', $projectId)
            ->whereIn('p.type', ['meeting', 'review'])
            ->where('task.step_type', '<>', 'step')
            ->whereIn('tm.partner', $directorUserIds)
            ->whereBetween("task.plan_end_time", [$todayStartTime, $todayEndTime])
            ->whereFieldRaw("get_json_string(`task`.`json`,'task_status')", '<>', $closedStatusId)
            ->find();
        if (!empty($tmpTodayProgressTotal)) {
            $tmpTodayProgressTotal = intval($tmpTodayProgressTotal['done_count']);
        }
        // 今日完成数
        $tmpTodayProgressDone = 0;
        if (!empty($directorUserIds)) {
            $tmpTodayProgressDone = (new \app\model\TaskModel)->field("count(task.id) today_done_task")
                ->join('task_member tm', 'task.id = tm.task_id')->join('plan p', 'task.id = p.task_id')
                ->where('p.project_id', $projectId)
                ->whereIn('p.type', ['meeting', 'review'])
                ->where('task.step_type', '<>', 'step')
                ->whereFieldRaw("get_json_string(`task`.`json`,'task_status')", '<>', $closedStatusId)
                ->whereIn('tm.partner', $directorUserIds)
                ->whereFieldRaw("get_json_string(`task`.`json`,'task_status')", '=', $finalStatusId)
                ->whereBetween("task.plan_end_time", [$todayStartTime, $todayEndTime])
                ->find();
            if (!empty($tmpTodayProgressDone)) {
                $tmpTodayProgressDone = intval($tmpTodayProgressDone['today_done_task']);
            }
        }
        // 导演-角色 director
        return [
            'type' => 'role',
            'category_id_list' => [],
            'category_name' => '导演',
            'total_progress_total' => $tmpTotalProgressTotal,
            'total_progress_done' => $tmpTotalProgressDone,
            'today_progress_total' => $tmpTodayProgressTotal,// 今日总数
            'today_progress_done' => $tmpTodayProgressDone,// 今日完成数
            'current_month_should_done' => []
        ];
    }

    /**
     * 查询本月交付异常情况
     * @return void
     */
    public function getMonthEpisodeAbnormalData($pqRes, $projectId)
    {
        $nowTime = time();
        $episodeIds = [];
        $stepCategoryIds = [];
        $epCategoryFilter = "(";
        foreach ($pqRes as $re) {
            $epCategoryFilter .= sprintf("(r_episode.link_id = %d and r_step_category.link_id = %d) OR", $re['episode_id'], $re['step_category_id']);
            $episodeIds[] = $re['episode_id'];
            $stepCategoryIds[] = $re['step_category_id'];
        }

        $closedStatusId = StatusService::getInstance()->byCode('closed')['id'];
        $epCategoryFilter = trim($epCategoryFilter, ' OR') . ')';
        $sql = 'select temp.step_category_id step_category_id,temp.episode_id episode_id,';
        $sql .= "count(temp.id) abnormal_number ";
        $sql .= "from (select task.id id, r_step_category.link_id step_category_id, r_episode.link_id episode_id,status.correspond,";
        $sql .= "if(status.correspond = 'not_started' and task.plan_start_time > 0 and task.plan_start_time < $nowTime, $nowTime - task.plan_start_time, 0) not_start_time, ";
        $sql .= "if(status.correspond not in ('not_started', 'done', 'blocked') and task.plan_end_time > 0 and task.plan_end_time < $nowTime, $nowTime - task.plan_end_time, 0) not_done_time, ";
        $sql .= "if(two.task_id > 0 and two.created < $nowTime, $nowTime - two.created, 0) work_order_not_handle_time ";
        $sql .= sprintf("from task join (select * from task_relation_tag where project_id = %d and link_module_code in ('show_step_category', 'step_category') and link_id in (%s)) r_step_category on  r_step_category.task_id = task.id ", $projectId, join(',', $stepCategoryIds));
        $sql .= sprintf("join (select * from task_relation_tag where project_id = %d and link_module_code = 'episode' and link_id in (%s)) r_episode on r_episode.task_id = task.id", $projectId, join(',', $episodeIds));
        $sql .= " left join (select min(created) created, task_id from task_work_order where status = 0 group by task_id) two on task.id = two.task_id ";
        $sql .= " left join status on status.id = get_json_string(task.`json`, '$.task_status') ";
        $sql .= sprintf("where task.project_id = %d and $epCategoryFilter ", $projectId);
        $sql .= "and task.step_type not in ('step') and get_json_string(`task`.`json`,'task_status') <> $closedStatusId ";
        $sql .= "and ((task.plan_start_time > 0 and task.plan_start_time < $nowTime and status.correspond in ('not_started')) or (task.plan_end_time > 0 and task.plan_end_time < $nowTime and status.correspond not in ('not_started', 'done')) or two.task_id > 0)) temp ";
        $sql .= "where (temp.not_start_time > 24 * 60 * 60) or (temp.not_done_time > 24 * 60 * 60) or (temp.work_order_not_handle_time > 24 * 60 * 60) ";
        $sql .= "group by temp.step_category_id, temp.episode_id";
        $list = Db::query($sql);
        if (empty($list)) {
            return [];
        }
        return $list;
    }

    /**
     * 查询剧集工序分类任务进度
     * @param $pqRes
     * @param $projectId
     * @return array
     */
    public function getEpisodeStepCategoryCountList($pqRes, $projectId): array
    {
        if (empty($pqRes)) {
            return [];
        }
        $episodeIds = [];
        $episodeMap = [];
        $stepCategoryIds = [];
        $stepCategoryMap = [];
        $epCategoryFilter = '(';
        foreach ($pqRes as $re) {
            $epCategoryFilter .= sprintf("(r_episode.link_id = %d and r_step_category.link_id = %d) OR ", $re['episode_id'], $re['step_category_id']);
            // 去重
            if (!isset($episodeMap[$re['episode_id']])) {
                $episodeMap[$re['episode_id']] = true;
                $episodeIds[] = $re['episode_id'];
            }
            // 去重
            if (!isset($stepCategoryMap[$re['step_category_id']])) {
                $stepCategoryMap[$re['step_category_id']] = true;
                $stepCategoryIds[] = $re['step_category_id'];
            }
        }
        $epCategoryFilter = trim($epCategoryFilter, ' OR') . ')';

        //获取剧集任务环节进度数据
        $progressDataList = $this->getEpisodeTaskStepCategoryProgressData(
            $epCategoryFilter,
            sprintf("link_id in (%s)", join(',', $stepCategoryIds)),
            sprintf("link_id in (%s)", join(',', $episodeIds)),
            $projectId,
            false
        );
        $res = [];
        foreach ($progressDataList as $progressDataItem) {
            $res[] = [
                'episode_id' => $progressDataItem['episode_id'],
                'episode_code' => '',
                'step_category_id' => $progressDataItem['step_category_id'],
                'step_category_name' => '',
                'total_count' => $progressDataItem['count'],
                'done_count' => $progressDataItem['complete_count']
            ];
        }
        return $res;
    }

    /**
     * 查询异常情况
     * @param $projectId
     * @param $showStepCategoryIdList
     * @return array
     */
    public function getTotalTodayAbnormalData($projectId, $showStepCategoryIdList): array
    {
        $nowTime = time();
        $sql = "select temp.step_category_id,";
        $sql .= "count((not_start_time > 30 * 24 * 60 * 60) or (not_done_time > 30 * 24 * 60 * 60) or (work_order_not_handle_time > 30 * 24 * 60 * 60) or null) total_abnormal_number,";
        $sql .= "count((not_start_time > 24 * 60 * 60) or (not_done_time > 24 * 60 * 60) or (work_order_not_handle_time > 24 * 60 * 60) or null) today_abnormal_number ";
        $sql .= "from (select task.id id,task.step_category_id step_category_id,status.correspond,if(status.correspond = 'not_started' and task.plan_start_time > 0 and task.plan_start_time < $nowTime,";
        $sql .= "$nowTime - task.plan_start_time, 0) not_start_time,";
        $sql .= "if(status.correspond not in ('not_started', 'done', 'blocked') and task.plan_end_time > 0 and task.plan_end_time < $nowTime,$nowTime - task.plan_end_time, 0) not_done_time,";
        $sql .= "if(two.task_id > 0 and two.created < $nowTime, $nowTime - two.created, 0) work_order_not_handle_time ";
        $sql .= "from task ";
        $sql .= "left join (select min(created) created, task_id from task_work_order where status = 0 group by task_id) two on task.id = two.task_id ";
        $sql .= "left join status on status.id = get_json_string(`task`.`json`,'task_status') ";
        $sql .= sprintf("where task.project_id = %d and task.step_category_id in (%s) and task.step_type = 'step' and get_json_string(`task`.`json`,'task_status') <> 3 and ( ", $projectId, join(',', $showStepCategoryIdList));
        $sql .= "(task.plan_start_time > 0 and task.plan_start_time < $nowTime and status.correspond in ('not_started') ) ";
        $sql .= "or ( task.plan_end_time > 0 and task.plan_end_time < $nowTime and status.correspond not in ('not_started', 'done') ) ";
        $sql .= "or two.task_id > 0 ) ) temp ";
        $sql .= "group by temp.step_category_id";
        $list = Db::query($sql);
        if (empty($list)) {
            return [];
        }
        return $list;
    }

    /**
     * 审核反馈统计
     * @param Request $request
     * @param int $reviewFeedbackId
     * @param int $projectId
     * @param string $episodeCode
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function reviewFeedbackCount(Request $request, int $reviewFeedbackId, int $projectId, string $episodeCode): array
    {
        $res = [];
        $projectShowStepCategoryList = OptionsService::getInstance()->GetProjectShowStepCategoryListConfig();

        $stepCategoryIds = array_column($projectShowStepCategoryList, 'id');
        $parentStepCategoryId = array_column($projectShowStepCategoryList, 'parent_id');
        $parentStepCategoryId = array_unique($parentStepCategoryId);

        // 返修了的
        $repairResult = NoteEntityRelationModel::join('note', 'note.id = note_entity_relation.note_id')
            ->join('entity', 'entity.id = note_entity_relation.entity_id')
            ->where('entity.initial_episode_code', $episodeCode)
            ->where('note.review_feedback_id', $reviewFeedbackId)
            ->whereIn('note.demand_status', ['processing', 'final'])
            ->whereIn('note_entity_relation.step_category_id', $stepCategoryIds)
            ->field('note_entity_relation.entity_id as entity_id'
                . ', note_entity_relation.step_category_id as step_category_id')
            ->group('note_entity_relation.entity_id'
                . ', note_entity_relation.step_category_id')
            ->select()->toArray();

        $repairCountMap = [];
        foreach ($repairResult as $item) {
            if (!isset($repairCountMap[$item['step_category_id']])) {
                $repairCountMap[$item['step_category_id']] = 0;
            }
            $repairCountMap[$item['step_category_id']]++;
        }

        $parentStepCategoryList = StepCategoryModel::whereIn('id', $parentStepCategoryId)
            ->select()->toArray();
        $pStepCategoryModuleMap = array_combine(array_column($parentStepCategoryList, 'id'),
            array_column($parentStepCategoryList, 'entity_id'));

        $moduleIdList = array_column($parentStepCategoryList, 'entity_id');
        $moduleIdList = array_unique($moduleIdList);

        $entityCountMap = self::getEntityModuleCount($projectId, $moduleIdList, $episodeCode);

        $resIndexMap = [];
        foreach ($projectShowStepCategoryList as $item) {
            $itemIndex = $item['show_name'];
            if (!isset($resIndexMap[$itemIndex])) {
                $res[] = [
                    'name' => $item['show_name'],
                    'color' => $item['color'],
                    'count' => intval($entityCountMap[$pStepCategoryModuleMap[$item['parent_id']]] ?? 0),
                    'repair_count' => intval($repairCountMap[$item['id']] ?? 0),
                    'progress' => 0,
                ];
                $resIndexMap[$itemIndex] = array_key_last($res);
            } else {
                $res[$resIndexMap[$itemIndex]]['repair_count'] += intval($repairCountMap[$item['id']] ?? 0);
            }
        }

        foreach ($res as &$item) {
            $item['progress'] = empty($item['count']) ? 0 : (100 * doubleval($item['repair_count']) / doubleval($item['count']));
        }
        return result_array_construct([
            'name' => 'strval',
            'color' => 'strval',
            'count' => 'intval',
            'repair_count' => 'intval',
            'progress' => 'intval',
        ], $res);
    }

    /**
     * @param int $projectId
     * @param array $moduleIds
     * @param string $initEpisodeCodes
     * @return mixed
     */
    private function getEntityModuleCount(int $projectId, array $moduleIds, string $initEpisodeCodes)
    {
        $res = [];
        // Log: status & module query is commented.

        return EntityModel::where('project_id', $projectId)
            ->whereIn('module_id', $moduleIds)
            ->where('initial_episode_code', $initEpisodeCodes)
            ->field('module_id'
                . ', count(entity_id) as count')
            ->group('module_id')
            ->select()->toArray();
    }
}
